﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Linq;
using MISAPP.Common;
using System.Data.Common;

namespace MISAPP.Classify.DataAccess
{
    public class DA_CIB001:DA_Matrix
    {        
        public DA_CIB001()
        {
            tableName = "企金";
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="p_checkAccount"></param>
        /// <param name="p_key"></param>
        /// <returns></returns>
        public DataTable GetDetailData(bool p_checkAccount,string p_accno)
        {
            string sql;

            sql = GetDetailSqlStatement();
            FillTableSchema(tableName);
            GetCriteriaString();
            #region 2012-1-04 明細也要加入rm
            string grpTable,fld;
            grpTable = string.Empty;
            fld = string.Empty;
            if (OrderByString.IndexOf("RM_GROUP") > -1)
            {
                grpTable = " RIGHT OUTER JOIN RM B ON A.LNAC_ID = B.LNAC_ID";
                fld = ",RM_GROUP";
            }
            #endregion
            sql = string.Format(sql
                    , CriteriaString
                    , " AND 1=1 "
                    , GetExclusiveOfAccount(p_checkAccount)
                    , OrderByString
                    , parseAccno(p_accno)
                    , grpTable
                    , fld
                    , Utility.GetCIBDetailField(string.Empty)
                    );
            AddParameter(sql);
            return getData();
        }

        public DataTable GetSummaryData(string p_groupBy, bool p_checkAccount
            ,string p_unit,string p_accno)
        {
            string sql, checkAccount,tmp1;
            checkAccount = GetExclusiveOfAccount(p_checkAccount);
            if (p_groupBy.IndexOf("指標利率") > -1)
            {
                p_groupBy = p_groupBy + ",利率說明";
            }

            #region 2011-12-01 要再檢查是否有選了客群
            string grpTable,str2 ;
            grpTable = string.Empty;
            str2 = string.Empty;
            if (p_groupBy.IndexOf("RM_GROUP") > -1)
            {
                grpTable = " A LEFT OUTER JOIN RM B ON A.LNAC_ID = B.LNAC_ID";
                str2 = " AND A.RM_GROUP = B.RM_GROUP";
            }
            #endregion


            GetGroupByString(p_groupBy);
            GetCriteriaString();
            GetOnString();
            tmp1 = groupString;
            if (p_groupBy.IndexOf("指標利率") > -1)
            {
                //tmp1 = extendFieldString.Replace("A.利率說明", "ISNULL(利率說明,'')利率說明");
                tmp1 = p_groupBy.Replace("利率說明", "ISNULL(利率說明,'')利率說明");
            }
            

            sql = string.Format(GetSummarySqlStatement()
                           , CriteriaString
                           , groupString
                           , onString + str2
                           , tmp1  
                           , extendFieldString 
                           , GetExclusiveOfAccount(p_checkAccount)
                           , p_unit
                           , parseAccno(p_accno)
                           , grpTable
                         );

            AddParameter(sql);
            return getData();
        }

        public override string[] GetDistinctField()
        {
            //return new string[]{"YYYYMM","風險等級"
            //                ,"CUR","會計科目"
            //                ,"區別","群組"
            //                ,"利率調整型態"};
            return new string[]{"YYYYMM","風險等級"
                            ,"CUR","會計科目"
                            ,"利率調整型態"};
        }
       
        public DataTable GetGroupBy()
        {
            DA_MISParameter para = new DA_MISParameter();
            DataTable dt = para.GetParameter("CIB", "GROUPBY");
            return dt;
            //return base.GetGroupBy("CIB", tableName);
        }
       
        public static DA_CIB001 GetInstance()
        {
            if (obj == null)
            {
                obj = InstanceHelper.CreateInstance("DA_CIB001");
            }
            return obj as DA_CIB001;
        }
        
        public override DataSet GetCriteria()
        {
            //DataSet ds =  base.GetCriteria();
            DataSet ds = new DataSet();
            DA_MISParameter para = new DA_MISParameter();
            DataTable dt = para.GetParameter("CIB", "EXCLUDE");
            dt.TableName = "LNAC_ID";
            ds.Tables.Add(dt);
            ds.Tables.Add(Utility.GetYYYYMMRange(Convert.ToDateTime("2009-09-01")));
            ds.Tables.Add(GetRiskLevelData());
            ds.Tables.Add(Utility.GetCIBCurData());
            ds.Tables.Add(Utility.GetCIBAccountNoData());
            ds.Tables.Add(Utility.GetCIBRateAdjType());
            
            return ds;
        }

        protected DataTable GetRiskLevelData()
        {
            return Utility.GetComplexlistboxTable("風險等級"
                , new string[] {"10","9","8","7","6","5","4",
                    "3","2","1","0"," " }
                , "風險等級");
        }

        

        protected string parseAccno(string p_accno)
        {
            string v1 = string.Empty;
            if (!string.Empty.EndsWith(p_accno))
            {
                p_accno = string.Format("{0}%", p_accno.Trim());
                v1 = " AND 帳號 LIKE @p_accNo";
                FillFieldList(new string[] { p_accno }
                    , new string[] { "@p_accNo" }, "帳號");
            }
            return v1;
        }

        protected override void FillTableSchema(string p_tableName)
        {
            base.FillTableSchema(p_tableName);
            tableSchema.Add("RM_GROUP", DbType.String);
        }

        #region sql statement

        /// <summary>
        /// {0}→Criteria
        /// {1}→ ORDER BY GROUP BY
        /// {2}→On
        /// {3}→內圈的欄位名
        /// {4}→最外圈的欄位名《extendFieldString》
        /// {5}→是否排除公庫
        /// {6}→計算單位
        /// {8}→加入RM TABLE  2011-12-01 ADD IN
        /// </summary>
        /// <returns></returns>
        protected string GetSummarySqlStatement()
        {
            return @"
                    SELECT * FROM (

                    SELECT ROW_NUMBER() OVER(ORDER BY {1})  AS SEQ,* 
                    FROM (

                        SELECT A.REC
									, CONVERT(NVARCHAR,CAST(ROUND(B.TOT_餘額/{6},2) AS MONEY),1) AS TOT_餘額
									, CONVERT(NVARCHAR,CAST(ROUND(B.TOT_BAL/{6},2) AS MONEY),1) AS TOT_均額
									, CONVERT(NVARCHAR,CAST(ROUND(A.SUM_CALFTP_RATE/B.TOT_BAL,7) AS MONEY),1) AS CALFTP_RATE
									, CONVERT(NVARCHAR,CAST(ROUND(A.SUM_CAL承作利率/B.TOT_BAL,7) AS MONEY),1) AS CAL承作利率
									, CONVERT(NVARCHAR,CAST(ROUND(A.SUM_CAL加減碼/B.TOT_BAL,7) AS MONEY),1) AS CAL加減碼 
                                    {4}  
                                FROM 
	                                (
		                                SELECT 
			                                COUNT(*) AS REC
			                                , SUM(FTP_RATE * 均額)  AS SUM_CALFTP_RATE
			                                , SUM(承作利率 * 均額) AS SUM_CAL承作利率
			                                , SUM(加減碼 * 均額)   AS SUM_CAL加減碼
			                                , {3}
		                                FROM 企金 {8}
		                                WHERE 均額 > 0 
				                                 {0}
                                                 {5}
                                                 {7}   
		                                GROUP BY {1}
                                        
	                                ) A INNER JOIN (
					                                SELECT 
						                                SUM(餘額) AS TOT_餘額
						                                , SUM(均額) AS TOT_BAL
						                                , {3}						                            
					                                FROM 企金 {8}
					                                WHERE 均額 > 0 
						                                {0}
                                                        {5}
                                                        {7}
					                                GROUP BY {1}                                                    
					                                )B
		                                ON {2}
		                            )A)A
                                    WHERE 1=1 
";
        }

        protected string GetDetailSqlStatement()
        {
            return @"
                    SELECT ROW_NUMBER() OVER( {3} )  AS SEQ,*
                    FROM(
                            SELECT
                                {7}
                                      {6}  
                                  FROM [企金] A {5}
                                  WHERE 均額 > 0
                                    {0}
                                    {1}
                                    {2}
                                    {4}
                                   
                        )A  {3}
";
        }

        #endregion
    }
}
